SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- Search Alternatives
/************ First Option *************/
--  IF  (@firstName    != NULL OR @firstName    != '') AND 
		--(@lastName     != NULL OR @lastName     != '') AND 
		--(@addressLine1 != NULL OR @addressLine1 != '') AND 
		--(@postalCode   != NULL OR @postalCode   != '') AND 
		--(@country      != NULL OR @country      != '') AND
		--(@phoneNumber  != NULL OR @phoneNumber  != '') AND 
		--(@email        != NULL OR @email        != '') AND 
		--(@entryDate    != NULL)
		--BEGIN
		--	SELECT * FROM dbo.vw_PersonDetails WHERE (
		--	FirstName    LIKE [dbo].[GetSearchString](@firstName)    OR
		--	LastName     LIKE [dbo].[GetSearchString](@lastName)     OR
		--	AddressLine1 LIKE [dbo].[GetSearchString](@addressLine1) OR
		--	Country      LIKE [dbo].[GetSearchString](@country)      OR
		--	PostalCode   LIKE [dbo].[GetSearchString](@postalCode)   OR
		--	PhoneNumber  LIKE [dbo].[GetSearchString](@phoneNumber)  OR
		--	Email        LIKE [dbo].[GetSearchString](@email)        OR
		--	EntryDate = @entryDate
		--	)
		--END
/*********** Second Option **********/
	  --DECLARE @sqlCommand VARCHAR(MAX)
	--SELECT @sqlCommand = 'SELECT Title, FirstName, LastName, AddressLine1, AddressLine2, AddressLine3, AddressLine4, '
	--SELECT @sqlCommand = @sqlCommand + 'PostalCode, Country, PhoneNumber, Email, EntryDate  FROM dbo.vw_PersonDetails WHERE 1=1'

	--IF  (@firstName != NULL OR @firstName != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND FirstName LIKE [dbo].[GetSearchString](''' + @firstName + ''')'
		
	--IF  (@lastName != NULL OR @lastName != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND LastName LIKE [dbo].[GetSearchString](''' + @lastName + ''')'
		
	--IF  (@addressLine1 != NULL OR @addressLine1 != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND AddressLine1 LIKE [dbo].[GetSearchString](''' + @addressLine1 + ''')'
		
	--IF  (@postalCode != NULL OR @postalCode != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND PostalCode LIKE [dbo].[GetSearchString](''' + @postalCode + ''')'
	
	--IF  (@country != NULL OR @country != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND Country LIKE [dbo].[GetSearchString](''' + @country + ''')'
	
	--IF  (@phoneNumber != NULL OR @phoneNumber != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND PhoneNumber LIKE [dbo].[GetSearchString](''' + @phoneNumber + ''')'
	
	--IF  (@email != NULL OR @email != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND Email LIKE [dbo].[GetSearchString](''' + @email + ''')'
	
	--IF (@entryDateFrom IS NOT NULL AND @entryDateTo IS NULL)
	--	SELECT @sqlCommand = @sqlCommand + ' AND EntryDate >= @entryDateFrom' 
	--ELSE IF (@entryDateFrom IS NULL AND @entryDateTo IS NOT NULL)
	--	SELECT @sqlCommand = @sqlCommand + ' AND EntryDate <= @entryDateTo'
	--ELSE IF (@entryDateFrom IS NOT NULL AND @entryDateTo IS NOT NULL)
	--	SELECT @sqlCommand = @sqlCommand + ' AND EntryDate >= @entryDateFrom AND EntryDate <= @entryDateTo'
	  
	--EXEC (@sqlCommand)

/*************** Third Option ******************/
		--   SELECT PersonID, Title, FirstName, LastName, AddressLine1, AddressLine2, AddressLine3, AddressLine4, 
	--	   PostalCode, Country, PhoneNumber, Email, EntryDate  
	--FROM dbo.vw_PersonDetails
	--WHERE(
	--	(CASE
	--		WHEN @firstname IS NULL THEN 1
	--		WHEN @firstname = '' THEN 1
	--		WHEN FirstName like [dbo].[GetSearchString](@firstName) THEN 1
	--		ELSE 0
	--	 END=1) AND
	--	(CASE 
	--		WHEN @lastName IS NULL 
	--		OR @lastname = '' 
	--		OR LastName like [dbo].[GetSearchString](@lastName) THEN 1
	--		ELSE 0
	--	 END=2) AND
	--	(CASE 
	--		WHEN @addressLine1 IS NULL
	--		OR @addressLine1 = ''
	--		OR AddressLine1 like [dbo].[GetSearchString](@addressLine1) THEN 1
	--		ELSE 0
	--	 END=3) AND
	--	(CASE 
	--		WHEN @postalCode IS NULL 
	--		OR @postalCode = '' 
	--		OR PostalCode like [dbo].[GetSearchString](@postalCode) THEN 1
	--		ELSE 0
	--	 END=4) AND
	--	(CASE 
	--		WHEN @country IS NULL
	--		OR @country = ''
	--		OR Country like [dbo].[GetSearchString](@country) THEN 1
	--		ELSE 0
	--	 END=5) AND
	--	(CASE 
	--		WHEN @phoneNumber IS NULL
	--		OR @phoneNumber = ''
	--		OR PhoneNumber like [dbo].[GetSearchString](@phoneNumber) THEN 1
	--		ELSE 0
	--	 END=6) AND
	--	(CASE 
	--		WHEN @email IS NULL
	--		OR @email = ''
	--		OR Email like [dbo].[GetSearchString](@email) THEN 1
	--		ELSE 0
	--	 END=7) AND
	--	(CASE 
	--		WHEN (@entryDateFrom IS NOT NULL AND @entryDateTo IS NULL) AND EntryDate >= @entryDateFrom
	--		OR (@entryDateFrom IS NULL AND @entryDateTo IS NOT NULL) AND EntryDate <= @entryDateTo
	--		OR (@entryDateFrom IS NOT NULL AND @entryDateTo IS NOT NULL) AND (EntryDate >= @entryDateFrom AND EntryDate <= @entryDateTo)
	--		THEN 1
	--		ELSE 0
	--	 END=8))
	--   '05-7-2011 6:11:20 PM', '17-7-2011 11:36:04 AM'
	
IF EXISTS (select * from sys.objects where object_id = object_id(N'[dbo].[SearchPersonDetails]') and type in (N'P', N'PC'))
    DROP PROCEDURE SearchPersonDetails
GO
 
CREATE PROCEDURE SearchPersonDetails
     @firstName       nvarchar(50)
    ,@lastName        nvarchar(50)
    ,@addressLine1    nvarchar(40)
    ,@postalCode      nvarchar(30)
    ,@country         nvarchar(50)
    ,@phoneNumber     nvarchar(50)
    ,@email           nvarchar(100)
    ,@entryDateFrom	  datetime
    ,@entryDateTo	  datetime
AS
BEGIN
    DECLARE @error int
    DECLARE @number_of_rows_affected int
    DECLARE @proc_error varchar (250) 
       SELECT PersonID, Title, FirstName, LastName, AddressLine1, AddressLine2, AddressLine3, AddressLine4, 
		   PostalCode, Country, PhoneNumber, Email, EntryDate  
	FROM dbo.vw_PersonDetails
	WHERE(
		(CASE
			WHEN @firstname IS NULL
			OR @firstname = ''
			OR FirstName like [dbo].[GetSearchString](@firstName) THEN 1
			ELSE 0
		 END=1) AND
		(CASE 
			WHEN @lastName IS NULL 
			OR @lastname = '' 
			OR LastName like [dbo].[GetSearchString](@lastName) THEN 1
			ELSE 0
		 END=1) AND
		(CASE 
			WHEN @addressLine1 IS NULL
			OR @addressLine1 = ''
			OR AddressLine1 like [dbo].[GetSearchString](@addressLine1)
			OR AddressLine2 like [dbo].[GetSearchString](@addressLine1)
			OR AddressLine3 like [dbo].[GetSearchString](@addressLine1)
			OR AddressLine4 like [dbo].[GetSearchString](@addressLine1) THEN 1
			ELSE 0
		 END=1) AND
		(CASE 
			WHEN @postalCode IS NULL 
			OR @postalCode = '' 
			OR PostalCode like [dbo].[GetSearchString](@postalCode) THEN 1
			ELSE 0
		 END=1) AND
		(CASE 
			WHEN @country IS NULL
			OR @country = ''
			OR Country like [dbo].[GetSearchString](@country) THEN 1
			ELSE 0
		 END=1) AND
		(CASE 
			WHEN @phoneNumber IS NULL
			OR @phoneNumber = ''
			OR PhoneNumber like [dbo].[GetSearchString](@phoneNumber) THEN 1
			ELSE 0
		 END=1) AND
		(CASE 
			WHEN @email IS NULL
			OR @email = ''
			OR Email like [dbo].[GetSearchString](@email) THEN 1
			ELSE 0
		 END=1) AND
		(CASE 
			WHEN (@entryDateFrom IS NOT NULL AND @entryDateTo IS NULL) AND EntryDate >= @entryDateFrom
			OR (@entryDateFrom IS NULL AND @entryDateTo IS NOT NULL) AND EntryDate <= @entryDateTo
			OR (@entryDateFrom IS NOT NULL AND @entryDateTo IS NOT NULL) AND (EntryDate >= @entryDateFrom AND EntryDate <= @entryDateTo)
			THEN 1
			ELSE 0
		 END=1))
 --  DECLARE @sqlCommand VARCHAR(MAX)
	--SELECT @sqlCommand = 'SELECT Title, FirstName, LastName, AddressLine1, AddressLine2, AddressLine3, AddressLine4, '
	--SELECT @sqlCommand = @sqlCommand + 'PostalCode, Country, PhoneNumber, Email, EntryDate  FROM dbo.vw_PersonDetails WHERE 1=1'

	--IF  (@firstName != NULL OR @firstName != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND FirstName LIKE [dbo].[GetSearchString](''' + @firstName + ''')'
		
	--IF  (@lastName != NULL OR @lastName != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND LastName LIKE [dbo].[GetSearchString](''' + @lastName + ''')'
		
	--IF  (@addressLine1 != NULL OR @addressLine1 != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND AddressLine1 LIKE [dbo].[GetSearchString](''' + @addressLine1 + ''')'
		
	--IF  (@postalCode != NULL OR @postalCode != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND PostalCode LIKE [dbo].[GetSearchString](''' + @postalCode + ''')'
	
	--IF  (@country != NULL OR @country != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND Country LIKE [dbo].[GetSearchString](''' + @country + ''')'
	
	--IF  (@phoneNumber != NULL OR @phoneNumber != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND PhoneNumber LIKE [dbo].[GetSearchString](''' + @phoneNumber + ''')'
	
	--IF  (@email != NULL OR @email != '') 
	--	SELECT @sqlCommand = @sqlCommand + ' AND Email LIKE [dbo].[GetSearchString](''' + @email + ''')'
	
	--IF (@entryDateFrom IS NOT NULL AND @entryDateTo IS NULL)
	--	SELECT @sqlCommand = @sqlCommand + ' AND EntryDate >= ' + @entryDateFrom + ''
	--ELSE IF (@entryDateFrom IS NULL AND @entryDateTo IS NOT NULL)
	--	SELECT @sqlCommand = @sqlCommand + ' AND EntryDate <= ' + @entryDateTo + ''
	--ELSE IF (@entryDateFrom IS NOT NULL AND @entryDateTo IS NOT NULL)
	--	SELECT @sqlCommand = @sqlCommand + ' AND EntryDate >= ' + @entryDateFrom + ' AND EntryDate <= ' + @entryDateTo + ''
	  
	--EXEC (@sqlCommand)	
 
    SET @number_of_rows_affected = @@ROWCOUNT
    SET @error = @@error
    IF @error<>0 
    BEGIN 
        select @proc_error = 'Error: ' + convert(varchar(20),@error)+ '. '+ description from master..sysmessages
            WHERE error = @error and  msglangid = 1033
        RAISERROR ('SearchPersonDetails failed',16, 1, @proc_error)
    END
END
GO


IF EXISTS (select * from sys.objects where object_id = object_id(N'[dbo].[SearchTransactionsByPersonID]') and type in (N'P', N'PC'))
DROP PROCEDURE SearchTransactionsByPersonID
GO
 
CREATE PROCEDURE SearchTransactionsByPersonID
     @personID        int
AS
BEGIN
	SELECT [PersonID]
      ,[TransactionID]
      ,[TransactionReceiptDetailID]
      ,[TransactionDate]
      ,[PersonName]
      ,[ModeName]
      ,[ModeNumber]
      ,[ModeBank]
      ,[ModeDate]
      ,[Account]
      ,[ReceiptNumber]
      ,[ReceiptDate]
      ,[SentBy]
   FROM [Sikra].[dbo].[vw_PersonTransactions]
	 WHERE [PersonID] = @personID
END
GO

